Release 10.1A: OpenEdge Application Server:
Developing WebSpeed Applications

Understanding the scope of database transactions

How does WebSpeed know where to start the database transaction and how much work to undo or back out? The following transaction blocks start a database transaction if one is not already active:

Any block that uses the TRANSACTION keyword on the block statement (DO, FOR EACH, or REPEAT).
A procedure block, trigger block, and each iteration of a DO ON ERROR, FOR EACH, or REPEAT block that directly updates the database or directly reads records with EXCLUSIVE-LOCK. You use EXCLUSIVE-LOCK to read records in multi-user applications.

Directly updating the database means that the block contains at least one statement that can change the database. CREATE, DELETE, and UPDATE are examples of such statements.

If a block contains FIND or FOR EACH statements that specify EXCLUSIVE-LOCK, and at least one of the FIND or FOR EACH statements is not embedded within inner transaction blocks, then the block is directly reading records with EXCLUSIVE-LOCK.

Note that DO blocks do not automatically have the transaction property. Also, if the procedure or database transaction you are looking at is run by another procedure, you must check the calling procedure to determine whether it starts a database transaction before the RUN statement.

Once a database transaction is started, all database changes are part of that transaction, until it ends. Each user of the database can have just one active transaction at a time. The procedure in Figure 8–4 has two blocks: the procedure block and the REPEAT block.

Figure 8–4: Database transaction scope

The procedure block has no statements directly in it that are not contained within the REPEAT block. The REPEAT block contains a CREATE statement that lets you add order records to the database. Because the REPEAT block is the outermost block that contains direct updates to the database, it is the transaction block.

At the start of an iteration of the REPEAT block, WebSpeed starts a database transaction. If any errors occur before the END statement, WebSpeed backs out any work done during that transaction.

Note that data-handling statements that cause WebSpeed to automatically start a database transaction for a regular table will not cause WebSpeed to automatically start a transaction for a work table or temporary table.

Figure 8–5 shows a procedure with multiple transactions.

Figure 8–5: Multiple DB transactions in a procedure

Note: This example uses the GetField method procedure where WebSpeed usage recommends the get-field() API function. See the online AppBuilder Help for more information.

This procedure has four blocks:

Procedure block — There are no statements in this block, so WebSpeed does not start a database transaction at the start of the procedure.
Outer REPEAT block — The outermost block that directly updates the database (CREATE order WITH 2 COLUMNS). Therefore, it is a transaction block. On each iteration of this block, WebSpeed starts a database transaction. If an error occurs before the end of the block, all work done in that iteration is undone.
Inner REPEAT block — Directly updates the database but it is not the outermost block to do so. Therefore, it is not a transaction block. It is, however, a subtransaction block. Subtransactions are discussed later in this chapter.
FOR EACH block — An outermost block that directly updates the database (UPDATE region). Therefore, it is a transaction block. On each iteration of this block, WebSpeed starts a database transaction. If an error occurs before the end of the block, all work done in that iteration is undone.